package com.siqi.hr.system.custom.db.builder;

import com.siqi.hr.system.common.authority.Sysdata;
import com.siqi.hr.system.common.sql.STable;
import com.siqi.hr.system.custom.db.builder.intf.DbCreater;
import com.siqi.hr.system.custom.entity.EntityRecord;
import com.siqi.hr.system.custom.entity.FieldRecord;
import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.util.Assert;

import java.lang.reflect.Constructor;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 添加自定义字段修改表结构类
 * @data      2016-08-30
 */
public class DbTableAlter {
    private static final Logger logger = Logger.getLogger(DbTableAlter.class);
    private EntityRecord entityRecord;
    private FieldRecord fieldRecord;

    public DbTableAlter(FieldRecord fieldRecord) {
        this.entityRecord = fieldRecord.getEntityRecord();
        this.fieldRecord = fieldRecord;
    }

    public DbCreater getDbCreater(TableAlterType type) {
        DbCreater creater = null;
        try {
            Class<?> _c = type.getDbCreater();
            Constructor<?> con = _c.getDeclaredConstructors()[0];
            creater = (DbCreater) con.newInstance(new Object[] { this });
        } catch (Exception e) {
            e.printStackTrace();
        }
        return creater;
    }

    /**
     * 判断字段名称是否在该数据库中已经存在(true:已经存在,false:不存在可以添加)
     * @param jdbcTemplate
     * @param tName
     * @param fName
     * @return
     */
    public boolean existField(JdbcTemplate jdbcTemplate, String tName,String fName) {
        String dbName = Sysdata.getInstance().getDatabase();
        StringBuffer sql = new StringBuffer("select count(0) from information_schema.`COLUMNS` where TABLE_SCHEMA = '").append(dbName+"' ");
        sql.append(" and TABLE_NAME = '").append(tName+"' and COLUMN_NAME = '").append(fName+"'");
        int exiFCount = jdbcTemplate.queryForObject(sql.toString(),Integer.class);
        return exiFCount > 0;
    }

    /**
     * 根据库名、表名、字段名查询主键是否注册(true:存在，false:不存在)
     * @param jdbcTemplate
     * @param tName
     * @param fName
     * @return
     */
    public boolean existForeginKey(JdbcTemplate jdbcTemplate, String tName,String fName) {
        String dbName = Sysdata.getInstance().getDatabase();
        StringBuffer sqlFk = new StringBuffer("select count(0) from information_schema.KEY_COLUMN_USAGE where TABLE_SCHEMA = '");
        sqlFk.append(dbName + "' and TABLE_NAME = '" + tName + "' and COLUMN_NAME = '" + fName + "' ");
        int exiFKCount = jdbcTemplate.queryForObject(sqlFk.toString(),Integer.class);
        return exiFKCount > 0;
    }

    public static enum TableAlterType {
        field_add(FieldAdd.class),
        field_delete(FieldDelete.class),
        key_delete(KeyDelete.class);

        private Class<? extends DbCreater> correspondingClass;

        private TableAlterType(Class<? extends DbCreater> cc) {
            this.correspondingClass = cc;
        }

        public Class<? extends DbCreater> getDbCreater() {
            return this.correspondingClass;
        }
    }

    public abstract class ATableAffluxJdbc implements DbCreater {
        protected JdbcTemplate jdbcTemplate;

        public ATableAffluxJdbc() {
        }

        public int create(JdbcTemplate jdbcTemplate) {
            Assert.notNull(jdbcTemplate, "JdbcTemplate未正确的注入");
            this.jdbcTemplate = jdbcTemplate;
            excuteTableAlter();
            return 0;
        }

        public abstract void excuteTableAlter();
    }

    /**
     * 添加子定义字段时修改表结构方法
     * @author xiaogoingqin
     *
     */
    public class FieldAdd extends ATableAffluxJdbc {
        public FieldAdd() {
            super();
        }

        public void excuteTableAlter() {
            String tName = STable.TABLE_HEAD+entityRecord.getTableCode();
            String fName = fieldRecord.getName();

            //定义要执行sql语句集合(xiaogq)
            List<String> sqlList = new ArrayList<String>();

            if (fName.startsWith("pk_")) {
                //如果当前添加的引用字段已经存在
                if (existField(jdbcTemplate, tName,fName)) {
                    //如果主键已经存在则调用存储过程删除相应外键
                    if (existForeginKey(jdbcTemplate,tName, fName)) {
                        try {
                            String dfksql = "CALL DELETE_FOREIGN_FIELD('"+ tName + "', '" + fName + "')";
                            jdbcTemplate.execute(dfksql);
                        } catch (Exception e) {
                            e.printStackTrace();
                        }
                    }

                    try {//添加字段时先删除该字段
                        StringBuffer dfsql = new StringBuffer("ALTER TABLE " + tName + " DROP COLUMN " + fName);
                        //加入删除表结构字段sql语句
                        sqlList.add(dfsql.toString());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }

            }
            //添加表字段sql语句
            StringBuilder sb = new StringBuilder(" alter table " + entityRecord.parse().getName() + " add column " + fieldRecord.toSqlString());
            sb.append("  comment '" +fieldRecord.getInchinese() + "'");
            //加入添加表结构字段sql语句
            sqlList.add(sb.toString());

            //如果添加的是单选、下拉类型的字段则添加外键
            if (fieldRecord.getName().endsWith("_radio") || fieldRecord.getName().endsWith("_singlebox")) {
                StringBuilder indexAddBuilder = new StringBuilder("alter table " + entityRecord.parse().getName());
                indexAddBuilder.append(" add CONSTRAINT " + entityRecord.parse().getName() + fieldRecord.getName());
                indexAddBuilder.append(" FOREIGN KEY (" + fieldRecord.getName()+") ");
                indexAddBuilder.append(" REFERENCES `sys_custom_selectbox` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;");
                //加入添加单选、下拉类型字段外键sql语句
                sqlList.add(indexAddBuilder.toString());
            }
            //如果是引用字段则添加外键
            if (fieldRecord.parse().isQuote()) {
                StringBuilder indexAddBuilder = new StringBuilder(" alter table " + entityRecord.parse().getName() + " add CONSTRAINT ");
                indexAddBuilder.append(entityRecord.parse().getName() + fieldRecord.getName() + fieldRecord.foreignKeyDefinition());
                if (fieldRecord.isUpRelation()) {
                    indexAddBuilder.append(" on update cascade ");
                }
                if (fieldRecord.isDeRelation()){
                    indexAddBuilder.append(" on delete cascade ");
                }
                //加入添加引用字段外键sql语句
                sqlList.add(indexAddBuilder.toString());
            }

            if(sqlList != null && sqlList.size() > 0){
                try {
                    //批量执行sql语句(添加字段是先删除表结构中的该字段，然后再添加该字段，如果是新建单选、下拉、引用类型的字段还得添加外键)
                    int [] count = jdbcTemplate.batchUpdate(sqlList.toArray(new String [sqlList.size()]));
                    if(count.length > 0){
                        //添加自定义字段时向字段注册表中注册字段信息
                        addFieldRecord();
                    }
                } catch (DataAccessException e) {
                    e.printStackTrace();
                }
            }
        }

        /**
         * 添加自定义字段时向字段注册表中注册字段信息(xiaogq)
         */
        public void addFieldRecord() {
            StringBuffer sql = new StringBuffer("insert into sys_custom_field(id,name,entity_id,inchinese,quote_entity_id,default_value,nulldisable,length,precisions,calfield,");
            sql.append("batch_update,read_only,value_unique,case_sensitive,first_as_default,can_order,edit_row_count,date_type,share_field_id,controled,sys_field,comment,position)");
            sql.append(" values( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
            //查询字段注册表中最大的字段id值
            final int id = this.jdbcTemplate.queryForObject("select max(id) from sys_custom_field",Integer.class) + 1;
            final int maxPosion = jdbcTemplate.queryForObject( "select max(position) from sys_custom_field where entity_id = " + DbTableAlter.this.fieldRecord.getEntityRecord().getTableCode(),Integer.class) + 1;
            this.jdbcTemplate.update(sql.toString(),new PreparedStatementSetter() {
                public void setValues(PreparedStatement ps) throws SQLException {
                    ps.setInt(1, id);
                    ps.setString(2,DbTableAlter.this.fieldRecord.getName());
                    ps.setInt(3, DbTableAlter.this.fieldRecord.getEntityRecord().getTableCode());
                    ps.setString(4,DbTableAlter.this.fieldRecord.getInchinese());
                    if(DbTableAlter.this.fieldRecord.getQuoteEntity() == null){
                        ps.setString(5, null);
                    }else{
                        ps.setInt(5,DbTableAlter.this.fieldRecord.getQuoteEntity().getTableCode());
                    }
                    ps.setString(6,DbTableAlter.this.fieldRecord.getDefaultValue());
                    ps.setBoolean(7,DbTableAlter.this.fieldRecord.isNulldisable());
                    ps.setInt(8, DbTableAlter.this.fieldRecord.getLength());
                    ps.setInt(9, DbTableAlter.this.fieldRecord.getPrecision());
                    ps.setBoolean(10,DbTableAlter.this.fieldRecord.isCalField());
                    ps.setBoolean(11,DbTableAlter.this.fieldRecord.isBatchUpdate());
                    ps.setBoolean(12,DbTableAlter.this.fieldRecord.isReadOnly());
                    ps.setBoolean(13,DbTableAlter.this.fieldRecord.isValueUnique());
                    ps.setBoolean(14,DbTableAlter.this.fieldRecord.isCaseSensitive());
                    ps.setBoolean(15,DbTableAlter.this.fieldRecord.isFirstAsDefault());
                    ps.setBoolean(16,DbTableAlter.this.fieldRecord.isCanOrder());
                    ps.setInt(17, DbTableAlter.this.fieldRecord.getEditRowCount());
                    ps.setInt(18, DbTableAlter.this.fieldRecord.getDateType());
                    if (DbTableAlter.this.fieldRecord.getShareFieldId() == null){
                        ps.setNull(19, 0);
                    }else {
                        ps.setInt(19,DbTableAlter.this.fieldRecord.getShareFieldId());
                    }
                    ps.setBoolean(20,DbTableAlter.this.fieldRecord.isControled());
                    ps.setBoolean(21,DbTableAlter.this.fieldRecord.isSysField());
                    ps.setString(22,DbTableAlter.this.fieldRecord.getComment());
                    ps.setInt(23,maxPosion);
                }
            });
            fieldRecord.setId(id);
        }

        public void rollbackDbException() {
            if (fieldRecord.parse().isQuote()){
                new KeyDelete().excuteTableAlter();
            }
            new FieldDelete().excuteTableAlter();
        }
    }

    public class KeyDelete extends ATableAffluxJdbc {
        public KeyDelete() {
            super();
        }

        public void excuteTableAlter() {
            StringBuilder dropColumnSql = new StringBuilder();
            dropColumnSql.append(" alter table ").append(DbTableAlter.this.entityRecord.getName());
            dropColumnSql.append(" DROP INDEX '").append(DbTableAlter.this.fieldRecord.getName()).append("'");
            this.jdbcTemplate.execute(dropColumnSql.toString());
        }

        public void rollbackDbException() {
        }
    }

    /**
     * 删除字段事相应的删除表结构中的字段
     * @author xiaogoingqin
     *
     */
    public class FieldDelete extends ATableAffluxJdbc {
        public FieldDelete() {
            super();
        }
        @Override
        public void excuteTableAlter() {
            String tName = STable.TABLE_HEAD + entityRecord.getTableCode();
            String fName = fieldRecord.getName();
            //如果字段在mysql字段注册表中已经存在
            if (existField(jdbcTemplate, tName, fName)) {
                List<String> sqlList= new ArrayList<String>();
                //如果删除的是引用、点选、下拉类型的字段则删除外键关系
                if (fName.startsWith("pk_") || fName.endsWith("_radio") || fName.endsWith("_singlebox")) {
                    //如果存在外键
                    if (existForeginKey(jdbcTemplate,tName, fName)) {
                        //删除外键sql语句
                        StringBuffer dfsql = new StringBuffer("ALTER TABLE " + tName + " DROP FOREIGN KEY " + entityRecord.parse().getName() + fName);
                        sqlList.add(dfsql.toString());
                    }
                }

                //删除字段注册表中的字段记录sql语句
                StringBuffer dropRecord = new StringBuffer("DELETE FROM sys_custom_field  WHERE id = " + fieldRecord.getId());
                sqlList.add(dropRecord.toString());
                //删除实体表结构中的字段
                StringBuffer dfsql = new StringBuffer("ALTER TABLE " + tName + " DROP COLUMN " + fName);
                sqlList.add(dfsql.toString());

                if(sqlList != null && sqlList.size() > 0){
                    try {
                        jdbcTemplate.batchUpdate(sqlList.toArray(new String [sqlList.size()]));
                    } catch (DataAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
        }

        public void rollbackDbException() {
        }
    }

}
